package com.lanyou.cook.service;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;

import org.apache.commons.collections4.map.MultiKeyMap;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.jxls.reader.ReaderBuilder;
import org.jxls.reader.XLSReader;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.Resource;
import org.springframework.core.io.ResourceLoader;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.xml.sax.SAXException;

import com.google.gson.JsonObject;
import com.lanyou.cook.common.Constants;
import com.lanyou.cook.datadictionary.UserEnableType;
import com.lanyou.cook.entity.domain.DBlink;
import com.lanyou.cook.entity.domain.DBlinkInterface;
import com.lanyou.cook.entity.domain.Field;
import com.lanyou.cook.entity.domain.FieldSystem;
import com.lanyou.cook.entity.domain.userManagement.Organization;
import com.lanyou.cook.entity.domain.userManagement.Role;
import com.lanyou.cook.entity.domain.userManagement.User;
import com.lanyou.cook.entitylistener.domain.UserEntityListenerForKettleObject;
import com.lanyou.cook.importdata.vo.BundleRelationVo;
import com.lanyou.cook.importdata.vo.DBlinkInterfaceVo;
import com.lanyou.cook.importdata.vo.DBlinkVo;
/*import com.lanyou.cook.importdata.vo.DBlinkVo;*/
import com.lanyou.cook.importdata.vo.FieldSystemVo;
import com.lanyou.cook.importdata.vo.FieldVo;
import com.lanyou.cook.importdata.vo.FtpScheduleVo;
import com.lanyou.cook.importdata.vo.FtpServerVo;
import com.lanyou.cook.importdata.vo.FtpVo;
import com.lanyou.cook.importdata.vo.HttpVo;
import com.lanyou.cook.importdata.vo.ParamVo;
import com.lanyou.cook.importdata.vo.ReqQuantityVo;
import com.lanyou.cook.importdata.vo.UserVo;
import com.lanyou.cook.importdata.vo.VisibleVo;
import com.lanyou.cook.repository.DBlinkInterfaceRepository;
import com.lanyou.cook.repository.DBlinkRepository;
import com.lanyou.cook.repository.FieldRepository;
import com.lanyou.cook.repository.FieldSystemRepository;
import com.lanyou.cook.repository.userManagement.OrganizationRepository;
import com.lanyou.cook.repository.userManagement.RoleRepository;
import com.lanyou.cook.repository.userManagement.UserRepository;
import com.lanyou.cook.util.ValidatorUtil;
import com.lanyou.esb.common.CommonUtils;
import com.lanyou.esb.datadictionary.EnableType;
import com.lanyou.esb.entity.DefinedBundle;
import com.lanyou.esb.entity.Http;
import com.lanyou.esb.entity.IfReqQuantity;
import com.lanyou.esb.exception.ServiceHandlerException;
import com.lanyou.esb.repository.DefinedBundleRepository;
import com.lanyou.esb.repository.FtpRepository;
import com.lanyou.esb.repository.HttpRepository;
import com.lanyou.esb.repository.IfReqQuantityRepository;
import com.lanyou.esb.service.ServiceManagerService;

/**
 * 
 * @author 曾族根、杨晨睿、仇伟业
 *
 */
@Component
public class ImportDataService {

	@Autowired
	private ResourceLoader resourceLoader;
	@Autowired
	private FieldRepository fieldRepository;
	@Autowired
	private FieldSystemRepository fieldSystemRepository;
	@Autowired
	private DBlinkRepository dblinkRepository;
	@Autowired
	private DBlinkInterfaceRepository dblinkInterfaceRepository;
	@Autowired
	private UserRepository userRepository;
	@Autowired
	private OrganizationRepository organizationRepository;
	@Autowired
	private RoleRepository roleRepository;
	
	@Autowired
	private  JdbcTemplate  jdbcTemplate;
	
	@Autowired
	private HttpRepository httpRepository;
	
	@Autowired
	private FtpRepository ftpRepository;
	
	
	@Autowired
	private EntityManager entityManager;
	
	@Autowired
	private DefinedBundleRepository definedBundleRepository;
	
	
	@Autowired
	private ServiceManagerService serviceManagerService;
	
	@Autowired
	private IfReqQuantityRepository ifReqQuantityRepository;

	/**
	 * 领域导入
	 * 
	 * @param xlsInputStream
	 *            类型：InputStream
	 * @return 返回Json格式的结果
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @throws SAXException
	 */
	@Transactional
	public String importField(InputStream xlsInputStream) throws InvalidFormatException, IOException, SAXException {
		List<FieldVo> vos = readFromXLS(xlsInputStream,
				resourceLoader.getResource("classpath:jxls/config/importField.xml"));
		System.out.println("vos:" + vos);
		JsonObject result = new JsonObject();
		List<Field> fields = new ArrayList<Field>();
		List<String> duplicateCheckList = new ArrayList<>();
		if (!vos.isEmpty()) {
			for (FieldVo vo : vos) {
				if (vo.getName() != null) {
					if (duplicateCheckList.isEmpty()) {
						duplicateCheckList.add(vo.getName());
					} else {
						if (duplicateCheckList.contains(vo.getName())) {
							result.addProperty("backValue", "领域名称 " + vo.getName() + " 重复！");
							fields.clear();
							return result.toString();
						} else {
							duplicateCheckList.add(vo.getName());
						}

					}

					Field uField = fieldRepository.findByName(vo.getName());
					if (uField != null) {
						if (vo.getRemark() != null) {
							uField.setRemark(vo.getRemark());
						}
						fields.add(uField);
					} else {
						Field aField = new Field();
						aField.setName(vo.getName());
						if (vo.getRemark() != null) {
							aField.setRemark(vo.getRemark());
						}
						fields.add(aField);
					}
				} else {
					result.addProperty("backValue", "领域名称不能为空！");
					fields.clear();
					return result.toString();
				}
			}
			if (!fields.isEmpty()) {
				for (Field field : fields) {
					fieldRepository.save(Arrays.asList(field));
				}
				result.addProperty("backValue", true);
			}
			return result.toString();
		} else {
			result.addProperty("backValue", false);
			return result.toString();
		}
	}

	/**
	 * 系统的导入
	 * 
	 * @param xlsInputStream
	 *            类型：InputStream
	 * @return 返回Json格式的结果
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @throws SAXException
	 */
	@Transactional
	public String importFieldSystem(InputStream xlsInputStream)
			throws InvalidFormatException, IOException, SAXException {
		List<FieldSystemVo> vos = readFromXLS(xlsInputStream,
				resourceLoader.getResource("classpath:jxls/config/importFieldSystem.xml"));
		JsonObject result = new JsonObject();
		List<FieldSystem> fieldSystems = new ArrayList<FieldSystem>();
		List<String> duplicateCheckList = new ArrayList<>();
		if (!vos.isEmpty()) {
			for (FieldSystemVo vo : vos) {
				if (vo.getFieldName() != null) {
					Field field = fieldRepository.findByName(vo.getFieldName());
					if (field != null) {
						if (vo.getName() != null) {
							if (duplicateCheckList.isEmpty()) {
								duplicateCheckList.add(vo.getName());
							} else {
								if (duplicateCheckList.contains(vo.getName())) {
									result.addProperty("backValue", "领域系统名称 " + vo.getName() + " 重复！");
									fieldSystems.clear();
									return result.toString();
								} else {
									duplicateCheckList.add(vo.getName());
								}

							}
							FieldSystem ufieldSystem = fieldSystemRepository.findByName(vo.getName());
							if (ufieldSystem != null) {
								ufieldSystem.setField(field);
								if(vo.getCode() != null){
									ufieldSystem.setCode(vo.getCode());
								}
								if (vo.getRemark() != null) {
									ufieldSystem.setRemark(vo.getRemark());
								}
								fieldSystems.add(ufieldSystem);
							} else {
								FieldSystem afieldSystem = new FieldSystem();
								afieldSystem.setField(field);
								afieldSystem.setName(vo.getName());
								if(vo.getCode() != null){
									afieldSystem.setCode(vo.getCode());
								}
								if (vo.getRemark() != null) {
									afieldSystem.setRemark(vo.getRemark());
								}
								fieldSystems.add(afieldSystem);
							}
						} else {
							result.addProperty("backValue", "领域系统名称不能为空!");
							fieldSystems.clear();
							return result.toString();
						}
					} else {
						result.addProperty("backValue", " 领域名称 " + vo.getFieldName() + " 不存在!");
						fieldSystems.clear();
						return result.toString();
					}
				} else {
					result.addProperty("backValue", "领域名称不能为空!");
					fieldSystems.clear();
					return result.toString();
				}
			}
			if (!fieldSystems.isEmpty()) {
				for (FieldSystem fieldSystem : fieldSystems) {
					fieldSystemRepository.save(Arrays.asList(fieldSystem));
				}
				result.addProperty("backValue", true);
			}
			return result.toString();
		} else {
			result.addProperty("backValue", false);
			return result.toString();
		}

	}

	// dblink接口Excle导入
	/**
	 * DBlink接口的导入
	 * 
	 * @param xlsInputStream
	 *            类型：InputStream
	 * @return 返回Json格式的结果
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @throws SAXException
	 */
	@Transactional
	public String importDBlinkInterface(InputStream xlsInputStream)
			throws InvalidFormatException, IOException, SAXException {
		List<DBlinkInterfaceVo> vos = readFromXLS(xlsInputStream,
				resourceLoader.getResource("classpath:jxls/config/importDBlinkInterface.xml"));
		System.out.println("vos:" + vos.toString());
		JsonObject result = new JsonObject();
		List<DBlinkInterface> dblinkInterfaces = new ArrayList<DBlinkInterface>();
		List<String> duplicateCheckList = new ArrayList<>();
		if (vos.isEmpty()) {
			result.addProperty("backValue", false);
			return result.toString();
		}
		for (DBlinkInterfaceVo vo : vos) {
			if (duplicateCheckList.isEmpty()) {
				duplicateCheckList.add(vo.getDblinkInterface());
			} else {
				if (duplicateCheckList.contains(vo.getDblinkInterface())) {
					result.addProperty("backValue", "导入表中存在重复的" + vo.getDblinkInterface());
					return result.toString();
				} else {
					duplicateCheckList.add(vo.getDblinkInterface());
				}
			}

			DBlinkInterface dblinkInterface = dblinkInterfaceRepository.findByname(vo.getDblinkInterface());
			if (dblinkInterface == null) {
				dblinkInterface = new DBlinkInterface();
			}
			if(vo.getDblinkInterface()!=null){
				dblinkInterface.setName(vo.getDblinkInterface());
			}else{
				result.addProperty("backValue", "dblink接口名不能为空!");
				return result.toString();
			}
			// 领域系统
			if (vo.getFieldSystemName() != null) {
				FieldSystem fieldSystem = fieldSystemRepository.findByName(vo.getFieldSystemName());
				if (fieldSystem != null) {
					if(StringUtils.equals(fieldSystem.getFieldName(), vo.getFieldName())){
						System.out.println(fieldSystem.getFieldName());
						dblinkInterface.setFieldSystem(fieldSystem);
					}else{
						result.addProperty("backValue", String.format("领域[%s]和系统[%s]不匹配，请检查数据的正确性!", vo.getFieldName(),vo.getFieldSystemName()));
						return result.toString();
					}
					dblinkInterface.setFieldSystem(fieldSystem);
				} else {
					result.addProperty("backValue", String.format("系统:[%s]不存在", vo.getFieldSystemName()));
					return result.toString();
				}
			} else {
				result.addProperty("backValue", "系统不能为空!");
				return result.toString();
			}
			if (vo.getFieldName() == null) {
				result.addProperty("backValue", "领域不能为空!");
				return result.toString();
			}
			if (vo.getDblinkName() != null) {
				DBlink dblink = dblinkRepository.findByname(vo.getDblinkName());
				if (dblink != null) {
					dblinkInterface.setDblink(dblink);
				} else {
					result.addProperty("backValue", String.format("dblink名为:[%s]不存在", vo.getDblinkName()));
					return result.toString();
				}
			} else {
				result.addProperty("backValue", "dblink不能为空!");
				return result.toString();
			}
			dblinkInterface.setRemark(vo.getRemark());
			dblinkInterfaces.add(dblinkInterface);
		}
		if (!dblinkInterfaces.isEmpty()) {
			for (DBlinkInterface dblinkInterface : dblinkInterfaces) {
				dblinkInterfaceRepository.save(Arrays.asList(dblinkInterface));
			}
			result.addProperty("backValue", true);
		}
		return result.toString();
	}

	/**
	 * DBlink的导入
	 * 
	 * @param xlsInputStream
	 *            类型：InputStream
	 * @return 返回Json格式的结果
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @throws SAXException
	 */
	// dblinkExcle导入
	@Transactional
	public String importDBlink(InputStream xlsInputStream) throws InvalidFormatException, IOException, SAXException {
		List<DBlinkVo> vos = readFromXLS(xlsInputStream,
				resourceLoader.getResource("classpath:jxls/config/importDBlink.xml"));
		System.out.println("vos:" + vos.toString());
		JsonObject result = new JsonObject();
		List<DBlink> dblinks = new ArrayList<DBlink>();
		if (vos.isEmpty()) {
			result.addProperty("backValue", false);
			return result.toString();
		}

		MultiKeyMap<String, Boolean> duplicateCheckMap = new MultiKeyMap<>();
		for (DBlinkVo vo : vos) {
			// 验证是否重名
			if (BooleanUtils.isTrue(duplicateCheckMap.get(vo.getName(), vo.getOwnerHost()))) {
				result.addProperty("backValue",
						String.format("导入数据中存在重复的数据:[%s]和[%s]", vo.getName(), vo.getOwnerHost()));
				return result.toString();
			}
			duplicateCheckMap.put(vo.getName(), vo.getOwnerHost(), true);

			DBlink dblink = dblinkRepository.findByNameAndOwnerHost(vo.getName(), vo.getOwnerHost());
			if (dblink == null) {
				dblink = new DBlink();
			}
			// 领域系统
			if (vo.getFieldSystemName() != null) {
				FieldSystem fieldSystem = fieldSystemRepository.findByName(vo.getFieldSystemName());
				if (fieldSystem != null) {
					if (vo.getFieldName() != null) {
						if(StringUtils.equals(fieldSystem.getFieldName(), vo.getFieldName())){
							dblink.setFieldSystem(fieldSystem);
						}else{
							result.addProperty("backValue", String.format("领域[%s]和系统[%s]不匹配，请检查数据的正确性!", vo.getFieldName(),vo.getFieldSystemName()));
							return result.toString();
						}
					}else{
						result.addProperty("backValue", "领域不能为空");
						return result.toString();
					}
					dblink.setFieldSystem(fieldSystem);
				} else {
					result.addProperty("backValue", String.format("系统:[%s]不存在", vo.getFieldSystemName()));
					return result.toString();
				}
			} else {
				result.addProperty("backValue", "系统名不能为空");
				return result.toString();
			}

			if (vo.getFieldName() == null) {
				result.addProperty("backValue", "领域不能为空");
				return result.toString();
			}
			if (vo.getName() != null) {
				dblink.setName(vo.getName());
			} else {
				result.addProperty("backValue", "dblink名不能为空");
				return result.toString();
			}
			if (vo.getOwnerHost() != null) {
				if(ValidatorUtil.isIPAddr(vo.getOwnerHost())){
				dblink.setOwnerHost(vo.getOwnerHost());
				}else{
					result.addProperty("backValue", String.format("源主机[%s]格式不正确，请修改！",vo.getOwnerHost()));
					return result.toString();
				}
			} else {
				result.addProperty("backValue", "源主机不能为空");
				return result.toString();
			}
			if (vo.getDestPassword() != null) {// 密码
				dblink.setDestPassword(vo.getDestPassword());
			} else {
				result.addProperty("backValue", "目标库密码不能为空!");
				return result.toString();
			}
			if (vo.getDestUserCode() != null) {// 账号
				dblink.setDestUserCode(vo.getDestUserCode());
			} else {
				result.addProperty("backValue", "目标库用户名不能为空!");
				return result.toString();
			}
			if (vo.getHost() != null) {// 主机
				if(ValidatorUtil.isIPAddr(vo.getHost())){
					dblink.setHost(vo.getHost());
				}else{
					result.addProperty("backValue", String.format("主机[%s]格式不正确，请修改！",vo.getHost()));
					return result.toString();
				}
				
			} else {
				result.addProperty("backValue", "主机不能为空!");
				return result.toString();
			}
			if (vo.getOwnerPort() != null) {// 源端口
				dblink.setOwnerPort(vo.getOwnerPort());
			} else {
				result.addProperty("backValue", "源端口不能为空!");
				return result.toString();
			}
			if (vo.getOwnerServiceName() != null) {// 源实例名
				dblink.setOwnerServiceName(vo.getOwnerServiceName());
			} else {
				result.addProperty("backValue", "源实例名不能为空!");
				return result.toString();
			}
			if (vo.getOwnerUser() != null) {// 源用户
				dblink.setOwnerUser(vo.getOwnerUser());
			} else {
				result.addProperty("backValue", "用户名不能为空!");
				return result.toString();
			}
			if (vo.getServiceName() != null) {// 实例名
				dblink.setServiceName(vo.getServiceName());
			} else {
				result.addProperty("backValue", "实例名不能为空!");
				return result.toString();
			}
			if (vo.getPort() != null) {//
				dblink.setServicePort(vo.getPort());
			} else {
				result.addProperty("backValue", "端口不能为空!");
				return result.toString();
			}
			dblinks.add(dblink);
		}
		if (!dblinks.isEmpty()) {
			for (DBlink dblink : dblinks) {
				dblinkRepository.save(dblink);
			}
			result.addProperty("backValue", true);
		}
		return result.toString();

	}

	private <T> List<T> readFromXLS(InputStream xlsInputStream, Resource configFile)
			throws IOException, SAXException, InvalidFormatException {

		List<T> result = new ArrayList<T>();
		Map<String, Object> beans = new HashMap<String, Object>();
		beans.put("result", result);

		XLSReader mainReader = ReaderBuilder.buildFromXML(configFile.getInputStream());
		mainReader.read(xlsInputStream, beans);

		return result;
	}

	/**
	 * 导入用户
	 * 
	 * @param xlsInputStream
	 *            类型：InputStream
	 * @return 返回Json格式的结果
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @throws SAXException
	 */
	@Transactional
	public String importUser(InputStream xlsInputStream) throws InvalidFormatException, IOException, SAXException {
		List<UserVo> vos = readFromXLS(xlsInputStream,
				resourceLoader.getResource("classpath:jxls/config/importUser.xml"));
		JsonObject result = new JsonObject();
		List<User> users = new ArrayList<User>();
		List<String> duplicateCheckList = new ArrayList<>();
		if (!vos.isEmpty()) {
			for (UserVo vo : vos) {
				if (vo.getUserCode() != null) {
					if (duplicateCheckList.isEmpty()) {
						duplicateCheckList.add(vo.getUserCode());
					} else {
						if (duplicateCheckList.contains(vo.getUserCode())) {
							result.addProperty("backValue", "帐号 " + vo.getUserCode() + " 重复！");
							users.clear();
							return result.toString();
						} else {
							duplicateCheckList.add(vo.getUserCode());
						}

					}
					// 第一步验证账号是否存在
					User uUser = userRepository.findByUserCode(vo.getUserCode());
					if (uUser != null) {
						// 部门
						if (vo.getDeptName() != null) {
							Organization organization = organizationRepository.findByOrgName(vo.getDeptName());
							if (organization != null) {
								Organization organizationParent = organization.getParent();
								if (StringUtils.equals(organizationParent.getOrgName(), vo.getCompanyName())) {
									uUser.setOrganization(organization);
								} else {
									result.addProperty("backValue", "此部门  "+vo.getDeptName()+" 的所属公司 "+vo.getCompanyName() + " 名称不存在！");
									users.clear();
									return result.toString();
								}
							} else {
								result.addProperty("backValue", vo.getDeptName() + " 部门名称不存在！");
								users.clear();
								return result.toString();
							}

						} else {
							result.addProperty("backValue", "部门名称不能为空！");
							users.clear();
							return result.toString();
						}
						// 角色
						if (vo.getRoleName() != null) {
							Role role = roleRepository.findByRoleName(vo.getRoleName());
							if (role != null) {
								uUser.setRole(role);
							} else {
								result.addProperty("backValue", vo.getRoleName() + " 角色名称不存在！");
								users.clear();
								return result.toString();
							}
						} else {
							result.addProperty("backValue", "角色名称不能为空！");
							users.clear();
							return result.toString();
						}

						if (vo.getUserName() != null) {
							if(!ValidatorUtil.isUserName(vo.getUserName())){
								result.addProperty("backValue", "姓名 "+vo.getUserName()+" 格式不正确，请输入长度介于1-10个字节且格式正确的姓名，如 张三！");
								users.clear();
								return result.toString();
							}
							uUser.setUserName(vo.getUserName());
						} else {
							result.addProperty("backValue", "用户姓名不能为空！");
							users.clear();
							return result.toString();
						}

						if (vo.getEmpNo() != null) {
							if(!ValidatorUtil.isEmpNo(vo.getEmpNo())){
								result.addProperty("backValue", "工号 "+vo.getEmpNo()+" 格式不正确，请输入长度介于1-20个字节且格式正确的工号，如 1123！");
								users.clear();
								return result.toString();
							}
							uUser.setEmpNo(vo.getEmpNo());
						}

						if (vo.getEmailAddress() != null) {
							if(!ValidatorUtil.isEmail(vo.getEmailAddress())){
								result.addProperty("backValue", "邮箱 "+vo.getEmailAddress()+" 格式不正确，请输入正确格式的邮箱地址，如 admin@szlanyou.com！");
								users.clear();
								return result.toString();
							}
							uUser.setEmailAddress(vo.getEmailAddress());
						}

						if (vo.getPhoneNumber() != null) {
							if(!ValidatorUtil.isMobile(vo.getPhoneNumber())){
								result.addProperty("backValue", "电话号码 "+vo.getPhoneNumber()+" 格式不正确，请输入正确格式的电话号码，如020-88888888,15888888888！");
								users.clear();
								return result.toString();
							}
							uUser.setPhoneNumber(vo.getPhoneNumber().toString());
						}

						if (vo.getRemark() != null) {
							uUser.setRemark(vo.getRemark());
						}
						
						uUser.setLoginStatus(UserEnableType.Enable.ValueCode);
						uUser.setPassword(Constants.EXPORT_USER_DEFAULT_PASSWORD);
						users.add(uUser);
					} else {
						User aUser = new User();
						// 部门
						if (vo.getDeptName() != null) {
							Organization organization = organizationRepository.findByOrgName(vo.getDeptName());
							if (organization != null) {
								Organization organizationParent = organization.getParent();
								if (StringUtils.equals(organizationParent.getOrgName(), vo.getCompanyName())) {
									aUser.setOrganization(organization);
								} else {
									result.addProperty("backValue", "此部门  "+vo.getDeptName()+" 的所属公司 "+vo.getCompanyName() + " 名称不存在！");
									users.clear();
									return result.toString();
								}
							} else {
								result.addProperty("backValue", vo.getDeptName() + " 部门名称不存在！");
								users.clear();
								return result.toString();
							}

						} else {
							result.addProperty("backValue", "部门名称不能为空！");
							users.clear();
							return result.toString();
						}
						// 角色
						if (vo.getRoleName() != null) {
							Role role = roleRepository.findByRoleName(vo.getRoleName());
							if (role != null) {
								aUser.setRole(role);
							} else {
								result.addProperty("backValue", vo.getRoleName() + " 角色名称不存在！");
								users.clear();
								return result.toString();
							}
						} else {
							result.addProperty("backValue", "角色名称不能为空！");
							users.clear();
							return result.toString();
						}

						if (vo.getUserCode() != null) {
							if(!ValidatorUtil.isUserCode(vo.getUserCode())){
								result.addProperty("backValue", "账号 "+vo.getUserCode()+" 格式不正确，请输入长度介于1-20个字节且格式正确的账号，如admin！");
								users.clear();
								return result.toString();
							}
							aUser.setUserCode(vo.getUserCode());
						} else {
							result.addProperty("backValue", "用户账号不能为空！");
							users.clear();
							return result.toString();
						}

						if (vo.getUserName() != null) {
							if(!ValidatorUtil.isUserName(vo.getUserName())){
								result.addProperty("backValue", "姓名 "+vo.getUserName()+" 格式不正确，请输入长度介于1-10个字节且格式正确的姓名，如 张三！");
								users.clear();
								return result.toString();
							}
							aUser.setUserName(vo.getUserName());
						} else {
							result.addProperty("backValue", "用户姓名不能为空！");
							users.clear();
							return result.toString();
						}

						if (vo.getEmpNo() != null) {
							if(!ValidatorUtil.isEmpNo(vo.getEmpNo())){
								result.addProperty("backValue", "工号 "+vo.getEmpNo()+" 格式不正确，请输入长度介于1-20个字节且格式正确的工号，如 1123！");
								users.clear();
								return result.toString();
							}
							aUser.setEmpNo(vo.getEmpNo());
						}

						if (vo.getEmailAddress() != null) {
							if(!ValidatorUtil.isEmail(vo.getEmailAddress())){
								result.addProperty("backValue", "邮箱地址 "+vo.getEmailAddress()+" 格式不正确，请输入正确格式的邮箱地址，如admin@szlanyou.com！");
								users.clear();
								return result.toString();
							}
							aUser.setEmailAddress(vo.getEmailAddress());
						}

						if (vo.getPhoneNumber() != null) {
							if(!ValidatorUtil.isMobile(vo.getPhoneNumber())){
								result.addProperty("backValue", "电话号码 "+vo.getPhoneNumber()+" 格式不正确，请输入正确格式的电话号码，如020-88888888,15888888888！");
								users.clear();
								return result.toString();
							}
							aUser.setPhoneNumber(vo.getPhoneNumber().toString());
						}

						if (vo.getRemark() != null) {
							aUser.setRemark(vo.getRemark());
						}

						aUser.setLoginStatus(UserEnableType.Enable.ValueCode);
						aUser.setPassword(Constants.EXPORT_USER_DEFAULT_PASSWORD);

						users.add(aUser);
					}
				} else {
					result.addProperty("backValue", "用户账号不能为空！");
					users.clear();
					return result.toString();
				}

			}
			if (!users.isEmpty()) {
				users.stream().forEach(u -> UserEntityListenerForKettleObject.setKettleUser(u));
				userRepository.save(users);
				result.addProperty("backValue", true);
			}
			return result.toString();
		} else {
			result.addProperty("backValue", false);
			return result.toString();
		}
	}
	
	
	
	/**
	 * 导入http接口
	 * 
	 * @param xlsInputStream
	 *            类型：InputStream
	 * @return 返回Json格式的结果
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @throws SAXException
	 */
	@Transactional
	public String importHttp(InputStream xlsInputStream) throws InvalidFormatException, IOException, SAXException {
		JsonObject result = new JsonObject();
		InputStream xmlInputStream = resourceLoader.getResource("classpath:jxls/config/importHttp.xml")
				.getInputStream();

		XLSReader reader = ReaderBuilder.buildFromXML(xmlInputStream);
		List<HttpVo> HttpVoList = new ArrayList<>();
		List<ParamVo> ParamVoList = new ArrayList<>();
		List<ReqQuantityVo> ReqQuantityVoList = new ArrayList<>();
		List<VisibleVo> visibleVoList = new ArrayList<>();
		List<BundleRelationVo> bundleRelationVoList = new ArrayList<>();

		Map<String, Object> beans = new HashMap<>();
		beans.put("HttpVoList", HttpVoList);
		beans.put("ParamVoList", ParamVoList);
		beans.put("ReqQuantityVoList", ReqQuantityVoList);
		beans.put("visibleVoList", visibleVoList);
		beans.put("bundleRelationVoList", bundleRelationVoList);
		
		reader.read(xlsInputStream, beans);
		
		if (HttpVoList.isEmpty()) {
			result.addProperty("backValue", false);
			result.addProperty("errorDesc", "接口基本信息数据不能为空！");
			return result.toString();
		}

		if (ReqQuantityVoList.isEmpty()) {
			result.addProperty("backValue", false);
			result.addProperty("errorDesc", "接口请求量信息数据不能为空！");
			return result.toString();
		}

		for (HttpVo httpVo : HttpVoList) {
			// 判断领域系统是否存在
			FieldSystem system = fieldSystemRepository.findByCode(httpVo.getSystemCode());
			if (system == null) {
				result.addProperty("backValue", false);
				result.addProperty("errorDesc", "接口" + httpVo.getName() + "的所属领域系统不存在！");
				return result.toString();
			}else{
				httpVo.setFieldSystemId(system.getId());
			}

			// 判断接口名称是否重名
			long countHttpN = httpRepository.countByNameAndIsEnable(httpVo.getName(), EnableType.ENABLE.ValueCode);
			if (countHttpN >= 1) {
				result.addProperty("backValue", false);
				result.addProperty("errorDesc", "接口名称" + httpVo.getName() + "已存在！");
				return result.toString();
			}

			// 判断接口编码是否存在
			long countHttpC = httpRepository.countByCodeAndIsEnable(httpVo.getCode(), EnableType.ENABLE.ValueCode);
			if (countHttpC >= 1) {
				result.addProperty("backValue", false);
				result.addProperty("errorDesc", "接口编码" + httpVo.getName() + "已存在！");
				return result.toString();
			}

		}

		for (BundleRelationVo bundleRelationVo : bundleRelationVoList) {
			// 判断接口关联的bundle名称是否存在
			DefinedBundle defindBundle = definedBundleRepository.findById(bundleRelationVo.getBundleId());
			if (defindBundle == null) {
				result.addProperty("backValue", false);
				result.addProperty("errorDesc", "BUNDLE的ID为" + bundleRelationVo.getBundleId() + "在数据库中不存在！");
				return result.toString();
			}
		}
		
		for(VisibleVo visibleVo : visibleVoList){
			FieldSystem system = fieldSystemRepository.findByCode(visibleVo.getSystemCode());
			if (system == null) {
				result.addProperty("backValue", false);
				result.addProperty("errorDesc", "接口的可见范围系统不存在！");
				return result.toString();
			}else{
				visibleVo.setIfVisibleSystemId(system.getId());
			}
		}

		String httpSQL = SQL_START_HTTP + SQL_END_HTTP;
		if (addHttps(httpSQL, HttpVoList) == null) {
			throw new ServiceHandlerException("执行接口基本信息的sql语句失败!", CommonUtils.CODE_EXECUTE_IF_SQL_FAILD);
		}
		String paramSQL = SQL_START_PARAM + SQL_END_PARAM;
		if (addParams(paramSQL, ParamVoList) == null) {
			throw new ServiceHandlerException("执行接口服务器信息的sql语句失败!", CommonUtils.CODE_EXECUTE_IF_SQL_FAILD);
		}
		String reqQuantitySQL = SQL_START_REQQUANTITY + SQL_END_REQQUANTITY;
		if (addReqQuantitys(reqQuantitySQL, ReqQuantityVoList) == null) {
			throw new ServiceHandlerException("执行接口请求量信息的sql语句失败!", CommonUtils.CODE_EXECUTE_IF_SQL_FAILD);
		}
		String visibleSQL = SQL_START_VISIBLE + SQL_END_VISIBLE;
		if (addVisibles(visibleSQL, visibleVoList) == null) {
			throw new ServiceHandlerException("执行接口可见范围的sql语句失败!", CommonUtils.CODE_EXECUTE_IF_SQL_FAILD);
		}
		String bundleRelationSQL = SQL_START_BUNDLERELATION + SQL_END_BUNDLERELATION;
		if (addBundleRelations(bundleRelationSQL, bundleRelationVoList) == null) {
			throw new ServiceHandlerException("执行接口bundle关系信息sql语句失败!", CommonUtils.CODE_EXECUTE_IF_SQL_FAILD);
		}

		//更新redis数据
		for (HttpVo httpVo : HttpVoList) {
			Http http = httpRepository.findOne(Http.idSpecification(httpVo.getId()));
			serviceManagerService.updateHttpRedisData(http);
		}
		
		for (ReqQuantityVo reqQuantityVo : ReqQuantityVoList) {
			IfReqQuantity ifReqQuantity = ifReqQuantityRepository.findOne(IfReqQuantity.idSpecification(reqQuantityVo.getId()));
			serviceManagerService.updateIfReqQuantityRedisData(ifReqQuantity);
		}
		
		result.addProperty("backValue", true);
		return result.toString();
	}
	
	
	/**
	 * 导入ftp接口
	 * 
	 * @param xlsInputStream
	 *            类型：InputStream
	 * @return 返回Json格式的结果
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @throws SAXException
	 */
	@Transactional
	public String importFtp(InputStream xlsInputStream) throws InvalidFormatException, IOException, SAXException {
		JsonObject result = new JsonObject();
		InputStream xmlInputStream = resourceLoader.getResource("classpath:jxls/config/importFtp.xml").getInputStream();

		XLSReader reader = ReaderBuilder.buildFromXML(xmlInputStream);
		List<FtpVo> FtpVoList = new ArrayList<>();
		List<FtpServerVo> FtpServerVoList = new ArrayList<>();
		List<FtpScheduleVo> FtpScheduleVoList = new ArrayList<>();
		List<VisibleVo> visibleVoList = new ArrayList<>();
		List<BundleRelationVo> bundleRelationVoList = new ArrayList<>();

		Map<String, Object> beans = new HashMap<>();
		beans.put("FtpVoList", FtpVoList);
		beans.put("FtpServerVoList", FtpServerVoList);
		beans.put("FtpScheduleVoList", FtpScheduleVoList);
		beans.put("visibleVoList", visibleVoList);
		beans.put("bundleRelationVoList", bundleRelationVoList);

		reader.read(xlsInputStream, beans);
		
		if (FtpVoList.isEmpty()) {
			result.addProperty("backValue", false);
			result.addProperty("errorDesc", "FTP接口数据不能为空！");
			return result.toString();
		}

		if (FtpVoList.isEmpty()) {
			result.addProperty("backValue", false);
			result.addProperty("errorDesc", "FTP接口服务器数据不能为空！");
			return result.toString();
		}
		for (FtpVo ftpVo : FtpVoList) {
			// 判断领域系统是否存在
			FieldSystem system = fieldSystemRepository.findByCode(ftpVo.getSystemCode());
			if (system == null) {
				result.addProperty("backValue", false);
				result.addProperty("errorDesc", "接口" + ftpVo.getName() + "的所属领域系统不存在！");
				return result.toString();
			}else{
				ftpVo.setFieldSystemId(system.getId());
			}

			// 判断接口名称是否重名
			long countFtpN = ftpRepository.countByInterfaceNameAndIsEnable(ftpVo.getName(),
					EnableType.ENABLE.ValueCode);
			if (countFtpN >= 1) {
				result.addProperty("backValue", false);
				result.addProperty("errorDesc", "接口名称" + ftpVo.getName() + "已存在！");
				return result.toString();
			}

			// 判断接口编码是否存在
			long countFtpC = ftpRepository.countByInterfacecodeAndIsEnable(ftpVo.getCode(),
					EnableType.ENABLE.ValueCode);
			if (countFtpC >= 1) {
				result.addProperty("backValue", false);
				result.addProperty("errorDesc", "接口编码" + ftpVo.getName() + "已存在！");
				return result.toString();
			}

		}
		
		for(FtpServerVo ftpServerVo : FtpServerVoList){
			FieldSystem system = fieldSystemRepository.findByCode(ftpServerVo.getSystemCode());
			if (system == null) {
				result.addProperty("backValue", false);
				result.addProperty("errorDesc", "接口服务器的所属系统不存在！");
				return result.toString();
			}else{
				ftpServerVo.setFieldSystemId(system.getId());
			}
		}

		for (BundleRelationVo bundleRelationVo : bundleRelationVoList) {
			// 判断接口关联的bundle名称是否存在
			DefinedBundle defindBundle = definedBundleRepository.findById(bundleRelationVo.getBundleId());
			if (defindBundle == null) {
				result.addProperty("backValue", false);
				result.addProperty("errorDesc", "BUNDLE的ID为" + bundleRelationVo.getBundleId() + "在数据库中不存在！");
				return result.toString();
			}
		}
		
		for(VisibleVo visibleVo : visibleVoList){
			FieldSystem system = fieldSystemRepository.findByCode(visibleVo.getSystemCode());
			if (system == null) {
				result.addProperty("backValue", false);
				result.addProperty("errorDesc", "接口的可见范围系统不存在！");
				return result.toString();
			}else{
				visibleVo.setIfVisibleSystemId(system.getId());
			}
		}

		
		String ftpSQL = SQL_START_FTP + SQL_END_FTP;
		if (addFtps(ftpSQL, FtpVoList) == null) {
			throw new ServiceHandlerException("执行ftp接口信息sql语句失败!", CommonUtils.CODE_EXECUTE_IF_SQL_FAILD);
		}
		String ftpServerSQL = SQL_START_FTPSERVER + SQL_END_FTPSERVER;
		if (addFtpServers(ftpServerSQL, FtpServerVoList) == null) {
			throw new ServiceHandlerException("执行ftp接口服务器信息的sql语句失败!", CommonUtils.CODE_EXECUTE_IF_SQL_FAILD);
		}
		String ftpScheduleSQL = SQL_START_FTPSCHEDULE + SQL_END_FTPSCHEDULE;
		if (addFtpSchedules(ftpScheduleSQL, FtpScheduleVoList) == null) {
			throw new ServiceHandlerException("执行ftp接口调度信息sql语句失败!", CommonUtils.CODE_EXECUTE_IF_SQL_FAILD);
		}
		String visibleSQL = SQL_START_VISIBLE + SQL_END_VISIBLE;
		if (addVisibles(visibleSQL, visibleVoList) == null) {
			throw new ServiceHandlerException("执行ftp接口可见范围sql语句失败!", CommonUtils.CODE_EXECUTE_IF_SQL_FAILD);
		}
		String bundleRelationSQL = SQL_START_BUNDLERELATION + SQL_END_BUNDLERELATION;
		if (addBundleRelations(bundleRelationSQL, bundleRelationVoList) == null) {
			throw new ServiceHandlerException("执行ftp接口bundle关系信息sql语句失败!", CommonUtils.CODE_EXECUTE_IF_SQL_FAILD);
		}

		result.addProperty("backValue", true);
		return result.toString();
	}

	// 接口动态sql组装
	private static final List<String> httpFields = new ArrayList<String>();

	/** SQL开头 **/
	private static final String SQL_START_HTTP = "INSERT INTO T_ESB_BU_IF_HTTP";
	/** SQL结尾 **/
	private static String SQL_END_HTTP = "";

	static {
		httpFields.add("IF_HTTP_ID");
		httpFields.add("FIELD_SYSTEM_ID");
		httpFields.add("SYSTEM_CODE");
		httpFields.add("IF_NAME");
		httpFields.add("IF_CODE");
		httpFields.add("BUNDLE_TYPE");
		httpFields.add("IS_PUBLIC");
		httpFields.add("AUTH_TYPE");
		httpFields.add("IF_TYPE");
		httpFields.add("IF_DESC");
		httpFields.add("IF_PRI_URL");
		httpFields.add("IF_PUB_URL");
		httpFields.add("IF_VERSION");
		httpFields.add("IF_STATUS");
		httpFields.add("IF_REQUEST");
		httpFields.add("DATA_FORMATE");
		httpFields.add("OVERTIME_TIMES");
		httpFields.add("IS_COST");
		httpFields.add("REMARK");
		httpFields.add("AUDIT_STATUS");
		httpFields.add("AUDIT_REMARK");
		httpFields.add("REQUEST_PARA_MESS");
		httpFields.add("RETURN_PARA_MESS");
		httpFields.add("IS_ISOLATION");
		httpFields.add("CONTENT_TYPE");
		httpFields.add("TRANSPORT_PROTOCOL");
		httpFields.add("IF_OPERATE_TYPE");
		httpFields.add("IS_TRACE");
		httpFields.add("SOAP_ACTION_URI");
		httpFields.add("IS_ENABLE");
		httpFields.add("CREATOR");
		httpFields.add("CREATED_DATE");
		httpFields.add("MODIFIER");
		httpFields.add("LAST_UPDATED_DATE");
	}

	static {
		StringBuilder builderFields = new StringBuilder();
		StringBuilder builderValues = new StringBuilder();
		builderFields.append(" (");
		builderValues.append(" (");
		int size = httpFields.size();
		for (int i = 0; i < size; ++i) {
			builderFields.append(httpFields.get(i) + (i == size - 1 ? "" : ","));
			builderValues.append((i == size - 1 ? "?" : "?,"));
		}
		builderFields.append(") ");
		builderValues.append(") ");
		SQL_END_HTTP = builderFields.toString() + " values " + builderValues.toString();
	}

	private int[] addHttps(final String sql, List<HttpVo> httpVos) {
		if (httpVos == null || httpVos.isEmpty()) {
			return new int[0];
		}
		final List<Object[]> args = new ArrayList<>();
		int i = 0;
		for (HttpVo httpVo : httpVos) {
			i = 0;
			Object[] objects = new Object[httpFields.size()];
			objects[i++] = httpVo.getId();
			objects[i++] = httpVo.getFieldSystemId();
			objects[i++] = httpVo.getSystemCode();
			objects[i++] = httpVo.getName();
			objects[i++] = httpVo.getCode();
			objects[i++] = httpVo.getBundleType();
			objects[i++] = httpVo.getIfPublic();
			objects[i++] = httpVo.getAuthType();
			objects[i++] = httpVo.getType();
			objects[i++] = httpVo.getDesc();
			objects[i++] = httpVo.getPriUrl();
			objects[i++] = httpVo.getPubUrl();
			objects[i++] = httpVo.getVersion();
			objects[i++] = httpVo.getStatus();
			objects[i++] = httpVo.getRequest();
			objects[i++] = httpVo.getDataFormate();
			objects[i++] = httpVo.getOvertime();
			objects[i++] = httpVo.getIfCost();
			objects[i++] = httpVo.getRemark();
			objects[i++] = httpVo.getAuditStatus();
			objects[i++] = httpVo.getAuditRemark();
			objects[i++] = httpVo.getRequestMess();
			objects[i++] = httpVo.getReturnMess();
			objects[i++] = httpVo.getIfOlation();
			objects[i++] = httpVo.getContentType();
			objects[i++] = httpVo.getTransportProtocol();
			objects[i++] = httpVo.getOperationType();
			objects[i++] = httpVo.getIsTrace();
			objects[i++] = httpVo.getSoapActionUri();
			objects[i++] = httpVo.getIsEnable();
			objects[i++] = httpVo.getCreator();
			objects[i++] = new Date();
			objects[i++] = httpVo.getModifier();
			objects[i++] = new Date();
			args.add(objects);
		}
		int[] result = null;
		try {
			return jdbcTemplate.batchUpdate(sql, args);
		} catch (Exception e) {
			e.printStackTrace();
			result = null;
		}
		return result;
	}

	// 接口请求参数动态sql组装
	private static final List<String> paramFields = new ArrayList<String>();

	/** SQL开头 **/
	private static final String SQL_START_PARAM = "INSERT INTO T_ESB_BU_IF_PARAMETER";
	/** SQL结尾 **/
	private static String SQL_END_PARAM = "";

	static {
		paramFields.add("IF_PARAMETER_ID");
		paramFields.add("IF_ID");
		paramFields.add("IF_PARA_TYPE");
		paramFields.add("PARA_NAME");
		paramFields.add("PARA_DATA_TYPE");
		paramFields.add("IS_NEED_PARA");
		paramFields.add("PARA_DESC");
		paramFields.add("PARA_POS");
		paramFields.add("PARA_DEFAULT_VALUE");
		paramFields.add("IS_ENABLE");
		paramFields.add("CREATOR");
		paramFields.add("CREATED_DATE");
		paramFields.add("MODIFIER");
		paramFields.add("LAST_UPDATED_DATE");
	}

	static {
		StringBuilder builderFields = new StringBuilder();
		StringBuilder builderValues = new StringBuilder();
		builderFields.append(" (");
		builderValues.append(" (");
		int size = paramFields.size();
		for (int i = 0; i < size; ++i) {
			builderFields.append(paramFields.get(i) + (i == size - 1 ? "" : ","));
			builderValues.append((i == size - 1 ? "?" : "?,"));
		}
		builderFields.append(") ");
		builderValues.append(") ");
		SQL_END_PARAM = builderFields.toString() + " values " + builderValues.toString();
	}

	private int[] addParams(final String sql, List<ParamVo> paramVos) {
		if (paramVos == null || paramVos.isEmpty()) {
			return new int[0];
		}
		final List<Object[]> args = new ArrayList<>();
		int i = 0;
		for (ParamVo paramVo : paramVos) {
			i = 0;
			Object[] objects = new Object[paramFields.size()];
			objects[i++] = paramVo.getId();
			objects[i++] = paramVo.getHttpId();
			objects[i++] = paramVo.getType();
			objects[i++] = paramVo.getName();
			objects[i++] = paramVo.getDataType();
			objects[i++] = paramVo.getIsNeed();
			objects[i++] = paramVo.getDesc();
			objects[i++] = paramVo.getPos();
			objects[i++] = paramVo.getDefaultValue();
			objects[i++] = paramVo.getIsEnable();
			objects[i++] = paramVo.getCreator();
			objects[i++] = new Date();
			objects[i++] = paramVo.getModifier();
			objects[i++] = new Date();
			args.add(objects);
		}
		int[] result = null;
		try {
			return jdbcTemplate.batchUpdate(sql, args);
		} catch (Exception e) {
			e.printStackTrace();
			result = null;
		}
		return result;
	}

	// 请求量动态sql组装
	private static final List<String> reqQuantityFields = new ArrayList<String>();

	/** SQL开头 **/
	private static final String SQL_START_REQQUANTITY = "INSERT INTO T_ESB_BU_IF_REQUEST_QTY";
	/** SQL结尾 **/
	private static String SQL_END_REQQUANTITY = "";

	static {
		reqQuantityFields.add("IF_REQUEST_QTY_ID");
		reqQuantityFields.add("IF_ID");
		reqQuantityFields.add("REQUEST_THRESHOLD");
		reqQuantityFields.add("MAX_REQUEST_QTY");
		reqQuantityFields.add("REQUEST_UNIT");
		reqQuantityFields.add("IS_ENABLE");
		reqQuantityFields.add("CREATOR");
		reqQuantityFields.add("CREATED_DATE");
		reqQuantityFields.add("MODIFIER");
		reqQuantityFields.add("LAST_UPDATED_DATE");
	}

	static {
		StringBuilder builderFields = new StringBuilder();
		StringBuilder builderValues = new StringBuilder();
		builderFields.append(" (");
		builderValues.append(" (");
		int size = reqQuantityFields.size();
		for (int i = 0; i < size; ++i) {
			builderFields.append(reqQuantityFields.get(i) + (i == size - 1 ? "" : ","));
			builderValues.append((i == size - 1 ? "?" : "?,"));
		}
		builderFields.append(") ");
		builderValues.append(") ");
		SQL_END_REQQUANTITY = builderFields.toString() + " values " + builderValues.toString();
	}

	private int[] addReqQuantitys(final String sql, List<ReqQuantityVo> reqQuantityVos) {
		if (reqQuantityVos == null || reqQuantityVos.isEmpty()) {
			return new int[0];
		}
		final List<Object[]> args = new ArrayList<>();
		int i = 0;
		for (ReqQuantityVo reqQuantityVo : reqQuantityVos) {
			i = 0;
			Object[] objects = new Object[reqQuantityFields.size()];
			objects[i++] = reqQuantityVo.getId();
			objects[i++] = reqQuantityVo.getHttpId();
			objects[i++] = reqQuantityVo.getRequestThreshold();
			objects[i++] = reqQuantityVo.getRequestMax();
			objects[i++] = reqQuantityVo.getRequestUnit();
			objects[i++] = reqQuantityVo.getIsEnable();
			objects[i++] = reqQuantityVo.getCreator();
			objects[i++] = new Date();
			objects[i++] = reqQuantityVo.getModifier();
			objects[i++] = new Date();
			args.add(objects);
		}
		int[] result = null;
		try {
			return jdbcTemplate.batchUpdate(sql, args);
		} catch (Exception e) {
			e.printStackTrace();
			result = null;
		}
		return result;
	}

	// 接口可见范围动态sql组装
	private static final List<String> visiblesFields = new ArrayList<String>();

	/** SQL开头 **/
	private static final String SQL_START_VISIBLE = "INSERT INTO T_ESB_BU_IF_VISIBLITY";
	/** SQL结尾 **/
	private static String SQL_END_VISIBLE = "";

	static {
		visiblesFields.add("IF_ID");
		visiblesFields.add("IF_VISIBLE_SYSTEM_ID");
		visiblesFields.add("IS_ENABLE");
		visiblesFields.add("CREATOR");
		visiblesFields.add("CREATED_DATE");
		visiblesFields.add("MODIFIER");
		visiblesFields.add("LAST_UPDATED_DATE");
	}

	static {
		StringBuilder builderFields = new StringBuilder();
		StringBuilder builderValues = new StringBuilder();
		builderFields.append(" (");
		builderValues.append(" (");
		int size = visiblesFields.size();
		for (int i = 0; i < size; ++i) {
			builderFields.append(visiblesFields.get(i) + (i == size - 1 ? "" : ","));
			builderValues.append((i == size - 1 ? "?" : "?,"));
		}
		builderFields.append(") ");
		builderValues.append(") ");
		SQL_END_VISIBLE = builderFields.toString() + " values " + builderValues.toString();
	}

	private int[] addVisibles(final String sql, List<VisibleVo> visibleVos) {
		if (visibleVos == null || visibleVos.isEmpty()) {
			return new int[0];
		}
		final List<Object[]> args = new ArrayList<>();
		int i = 0;
		for (VisibleVo visibleVo : visibleVos) {
			i = 0;
			Object[] objects = new Object[visiblesFields.size()];
			objects[i++] = visibleVo.getIfId();
			objects[i++] = visibleVo.getIfVisibleSystemId();
			objects[i++] = visibleVo.getIsEnable();
			objects[i++] = visibleVo.getCreator();
			objects[i++] = new Date();
			objects[i++] = visibleVo.getModifier();
			objects[i++] = new Date();
			args.add(objects);
		}
		int[] result = null;
		try {
			return jdbcTemplate.batchUpdate(sql, args);
		} catch (Exception e) {
			e.printStackTrace();
			result = null;
		}
		return result;
	}

	// 接口关联的bundle动态sql组装
	private static final List<String> bundleRelationFields = new ArrayList<String>();

	/** SQL开头 **/
	private static final String SQL_START_BUNDLERELATION = "INSERT INTO T_ESB_BU_IF_BUNDLES_RELATION";
	/** SQL结尾 **/
	private static String SQL_END_BUNDLERELATION = "";

	static {
		bundleRelationFields.add("IF_ID");
		bundleRelationFields.add("BUNDLE_ID");
		bundleRelationFields.add("IS_ENABLE");
		bundleRelationFields.add("CREATOR");
		bundleRelationFields.add("CREATED_DATE");
		bundleRelationFields.add("MODIFIER");
		bundleRelationFields.add("LAST_UPDATED_DATE");
	}

	static {
		StringBuilder builderFields = new StringBuilder();
		StringBuilder builderValues = new StringBuilder();
		builderFields.append(" (");
		builderValues.append(" (");
		int size = bundleRelationFields.size();
		for (int i = 0; i < size; ++i) {
			builderFields.append(bundleRelationFields.get(i) + (i == size - 1 ? "" : ","));
			builderValues.append((i == size - 1 ? "?" : "?,"));
		}
		builderFields.append(") ");
		builderValues.append(") ");
		SQL_END_BUNDLERELATION = builderFields.toString() + " values " + builderValues.toString();
	}

	private int[] addBundleRelations(final String sql, List<BundleRelationVo> bundleRelationVoS) {
		if (bundleRelationVoS == null || bundleRelationVoS.isEmpty()) {
			return new int[0];
		}
		final List<Object[]> args = new ArrayList<>();
		int i = 0;
		for (BundleRelationVo bundleRelationVo : bundleRelationVoS) {
			i = 0;
			Object[] objects = new Object[bundleRelationFields.size()];
			objects[i++] = bundleRelationVo.getIfId();
			objects[i++] = bundleRelationVo.getBundleId();
			objects[i++] = bundleRelationVo.getIsEnable();
			objects[i++] = bundleRelationVo.getCreator();
			objects[i++] = new Date();
			objects[i++] = bundleRelationVo.getModifier();
			objects[i++] = new Date();
			args.add(objects);
		}
		int[] result = null;
		try {
			return jdbcTemplate.batchUpdate(sql, args);
		} catch (Exception e) {
			e.printStackTrace();
			result = null;
		}
		return result;
	}

	

	// 接口动态sql组装
	private static final List<String> ftpFields = new ArrayList<String>();

	/** SQL开头 **/
	private static final String SQL_START_FTP = "INSERT INTO T_ESB_BU_IF_FTP";
	/** SQL结尾 **/
	private static String SQL_END_FTP = "";

	static {
		ftpFields.add("IF_FTP_ID");
		ftpFields.add("FIELD_SYSTEM_ID");
		ftpFields.add("SYSTEM_CODE");
		ftpFields.add("IF_NAME");
		ftpFields.add("IF_CODE");
		ftpFields.add("IF_VERSION");
		ftpFields.add("IF_STATUS");
		ftpFields.add("IF_DESC");
		ftpFields.add("OVERTIME_TIMES");
		ftpFields.add("AUDIT_STATUS");
		ftpFields.add("AUDIT_REMARK");
		ftpFields.add("IS_COST");
		ftpFields.add("REMARK");
		ftpFields.add("IS_PUBLIC");
		ftpFields.add("IF_TYPE");
		ftpFields.add("FILE_CACHE_PATH");
		ftpFields.add("IS_SCHEDULE");
		ftpFields.add("IS_ENABLE");
		ftpFields.add("CREATOR");
		ftpFields.add("CREATED_DATE");
		ftpFields.add("MODIFIER");
		ftpFields.add("LAST_UPDATED_DATE");
	}

	static {
		StringBuilder builderFields = new StringBuilder();
		StringBuilder builderValues = new StringBuilder();
		builderFields.append(" (");
		builderValues.append(" (");
		int size = ftpFields.size();
		for (int i = 0; i < size; ++i) {
			builderFields.append(ftpFields.get(i) + (i == size - 1 ? "" : ","));
			builderValues.append((i == size - 1 ? "?" : "?,"));
		}
		builderFields.append(") ");
		builderValues.append(") ");
		SQL_END_FTP = builderFields.toString() + " values " + builderValues.toString();
	}

	private int[] addFtps(final String sql, List<FtpVo> ftpVos) {
		if (ftpVos == null || ftpVos.isEmpty()) {
			return new int[0];
		}
		final List<Object[]> args = new ArrayList<>();
		int i = 0;
		for (FtpVo ftpVo : ftpVos) {
			i = 0;
			Object[] objects = new Object[ftpFields.size()];
			objects[i++] = ftpVo.getId();
			objects[i++] = ftpVo.getFieldSystemId();
			objects[i++] = ftpVo.getSystemCode();
			objects[i++] = ftpVo.getName();
			objects[i++] = ftpVo.getCode();
			objects[i++] = ftpVo.getVersion();
			objects[i++] = ftpVo.getStatus();
			objects[i++] = ftpVo.getDesc();
			objects[i++] = ftpVo.getOvertime();
			objects[i++] = ftpVo.getAuditStatus();
			objects[i++] = ftpVo.getAuditRemark();
			objects[i++] = ftpVo.getIfCost();
			objects[i++] = ftpVo.getRemark();
			objects[i++] = ftpVo.getIfPublic();
			objects[i++] = ftpVo.getType();
			objects[i++] = ftpVo.getFileCachePath();
			objects[i++] = ftpVo.getIsSchedule();
			objects[i++] = ftpVo.getIsEnable();
			objects[i++] = ftpVo.getCreator();
			objects[i++] = new Date();
			objects[i++] = ftpVo.getModifier();
			objects[i++] = new Date();
			args.add(objects);
		}
		int[] result = null;
		try {
			return jdbcTemplate.batchUpdate(sql, args);
		} catch (Exception e) {
			e.printStackTrace();
			result = null;
		}
		return result;
	}

	// 接口请求参数动态sql组装
	private static final List<String> ftpServerFields = new ArrayList<String>();

	/** SQL开头 **/
	private static final String SQL_START_FTPSERVER = "INSERT INTO T_ESB_BU_IF_FTP_SERVER";
	/** SQL结尾 **/
	private static String SQL_END_FTPSERVER = "";

	static {
		ftpServerFields.add("IF_FTP_SERVER_ID");
		ftpServerFields.add("IF_FTP_ID");
		ftpServerFields.add("HOST");
		ftpServerFields.add("PORT");
		ftpServerFields.add("USER_NAME");
		ftpServerFields.add("PASSWORD");
		ftpServerFields.add("IS_ANONYMOUS");
		ftpServerFields.add("IS_DEL_SOURCE");
		ftpServerFields.add("IS_COVER");
		ftpServerFields.add("FILE_DIR");
		ftpServerFields.add("HOST_TYPE");
		ftpServerFields.add("FIELD_SYSTEM_ID");
		ftpServerFields.add("FILE_NAME");
		ftpServerFields.add("CONTROL_ENCODING");
		ftpServerFields.add("IS_ENABLE");
		ftpServerFields.add("CREATOR");
		ftpServerFields.add("CREATED_DATE");
		ftpServerFields.add("MODIFIER");
		ftpServerFields.add("LAST_UPDATED_DATE");
	}

	static {
		StringBuilder builderFields = new StringBuilder();
		StringBuilder builderValues = new StringBuilder();
		builderFields.append(" (");
		builderValues.append(" (");
		int size = ftpServerFields.size();
		for (int i = 0; i < size; ++i) {
			builderFields.append(ftpServerFields.get(i) + (i == size - 1 ? "" : ","));
			builderValues.append((i == size - 1 ? "?" : "?,"));
		}
		builderFields.append(") ");
		builderValues.append(") ");
		SQL_END_FTPSERVER = builderFields.toString() + " values " + builderValues.toString();
	}

	private int[] addFtpServers(final String sql, List<FtpServerVo> ftpServerVos) {
		if (ftpServerVos == null || ftpServerVos.isEmpty()) {
			return new int[0];
		}
		final List<Object[]> args = new ArrayList<>();
		int i = 0;
		for (FtpServerVo ftpServerVo : ftpServerVos) {
			i = 0;
			Object[] objects = new Object[ftpServerFields.size()];
			objects[i++] = ftpServerVo.getId();
			objects[i++] = ftpServerVo.getFtpId();
			objects[i++] = ftpServerVo.getHost();
			objects[i++] = ftpServerVo.getPort();
			objects[i++] = ftpServerVo.getUserName();
			objects[i++] = ftpServerVo.getPassword();
			objects[i++] = ftpServerVo.getIfAnonymous();
			objects[i++] = ftpServerVo.getIfDelSource();
			objects[i++] = ftpServerVo.getIfCover();
			objects[i++] = ftpServerVo.getFileDir();
			objects[i++] = ftpServerVo.getHostType();
			objects[i++] = ftpServerVo.getFieldSystemId();
			objects[i++] = ftpServerVo.getFileName();
			objects[i++] = ftpServerVo.getServerCode();
			objects[i++] = ftpServerVo.getIsEnable();
			objects[i++] = ftpServerVo.getCreator();
			objects[i++] = new Date();
			objects[i++] = ftpServerVo.getModifier();
			objects[i++] = new Date();
			args.add(objects);
		}
		int[] result = null;
		try {
			return jdbcTemplate.batchUpdate(sql, args);
		} catch (Exception e) {
			e.printStackTrace();
			result = null;
		}
		return result;
	}

	// 请求量动态sql组装
	private static final List<String> ftpScheduleFields = new ArrayList<String>();

	/** SQL开头 **/
	private static final String SQL_START_FTPSCHEDULE = "INSERT INTO T_ESB_BU_IF_SCHEDULE";
	/** SQL结尾 **/
	private static String SQL_END_FTPSCHEDULE = "";

	static {
		ftpScheduleFields.add("IF_SCHEDULE_ID");
		ftpScheduleFields.add("IF_SCHEDULE_NAME");
		ftpScheduleFields.add("IF_ID");
		ftpScheduleFields.add("IF_TYPE");
		ftpScheduleFields.add("RUN_WAY");
		ftpScheduleFields.add("RUN_CYC");
		ftpScheduleFields.add("DAY_OF_WEEK");
		ftpScheduleFields.add("SKD_BEGIN_DATE");
		ftpScheduleFields.add("SKD_END_DATE");
		ftpScheduleFields.add("IS_ENABLE");
		ftpScheduleFields.add("CREATOR");
		ftpScheduleFields.add("CREATED_DATE");
		ftpScheduleFields.add("MODIFIER");
		ftpScheduleFields.add("LAST_UPDATED_DATE");
	}

	static {
		StringBuilder builderFields = new StringBuilder();
		StringBuilder builderValues = new StringBuilder();
		builderFields.append(" (");
		builderValues.append(" (");
		int size = ftpScheduleFields.size();
		for (int i = 0; i < size; ++i) {
			builderFields.append(ftpScheduleFields.get(i) + (i == size - 1 ? "" : ","));
			builderValues.append((i == size - 1 ? "?" : "?,"));
		}
		builderFields.append(") ");
		builderValues.append(") ");
		SQL_END_FTPSCHEDULE = builderFields.toString() + " values " + builderValues.toString();
	}

	private int[] addFtpSchedules(final String sql, List<FtpScheduleVo> ftpScheduleVos) {
		if (ftpScheduleVos == null || ftpScheduleVos.isEmpty()) {
			return new int[0];
		}
		final List<Object[]> args = new ArrayList<>();
		int i = 0;
		for (FtpScheduleVo ftpScheduleVo : ftpScheduleVos) {
			i = 0;
			Object[] objects = new Object[ftpScheduleFields.size()];
			objects[i++] = ftpScheduleVo.getId();
			objects[i++] = ftpScheduleVo.getName();
			objects[i++] = ftpScheduleVo.getFtpId();
			objects[i++] = ftpScheduleVo.getIfType();
			objects[i++] = ftpScheduleVo.getRunWay();
			objects[i++] = ftpScheduleVo.getRunCyc();
			objects[i++] = ftpScheduleVo.getDayOfWeek();
			objects[i++] = ftpScheduleVo.getBeginTime();
			objects[i++] = ftpScheduleVo.getEndTime();
			objects[i++] = ftpScheduleVo.getIsEnable();
			objects[i++] = ftpScheduleVo.getCreator();
			objects[i++] = new Date();
			objects[i++] = ftpScheduleVo.getModifier();
			objects[i++] = new Date();
			args.add(objects);
		}
		int[] result = null;
		try {
			return jdbcTemplate.batchUpdate(sql, args);
		} catch (Exception e) {
			e.printStackTrace();
			result = null;
		}
		return result;
	}
}
